# -*- coding: utf-8 -*-
"""
@File:    send_dao.py
@Author:  jk.dong
@Desc:    发送邮件的sql模块
"""
import traceback
import re
from docwork import logging, datetime, os
from docwork.utils import get_xml, sendmail
from docwork.config import (load_config, RUN_CONF, Path, BASE_PATH, table_docreport,
                        table_docgeneral, table_km_recvsendinfo, table_km_customerinfo,
                        table_entity, table_attachment, table_doctype, table_km_docrecievelog)
from docwork.dao.base_dao import (BaseDao, deal_date, db1_type, db1_address,
                                  db1_name, db1_port, db1_username, db1_password)


# 初始化数据库对象
SEND_DB = BaseDao(db1_type, username=db1_username, password=db1_password, address=db1_address, dbname=db1_name,
                  dbport=db1_port)
SEND_CONF = load_config(section=['run-setting', 'send-setting', 'import-setting'])
if not SEND_CONF['send-setting'].get('send_protocol'):
    SEND_CONF['send-setting']['send_protocol'] = 'SMTP'


class SendDao:
    def __init__(self, config_):
        self.date_now = datetime.datetime.now()
        self.delay_minutes = config_['send-setting'].get('delay_minutes')
        self.include_thread_id = config_['send-setting'].get('include_thread_id')
        self.include_customer_limit = config_['send-setting'].get('include_customer_limit')
        self.exclude_customer_limit = config_['send-setting'].get('exclude_customer_limit')
        self.exclude_broker_list = config_['send-setting'].get('exclude_broker_list')
        self.offset_days = config_['send-setting'].get('offset_days')
        self.doc_send_status_list = config_['send-setting'].get('doc_send_status_list')
        self.doc_send_status = config_['send-setting'].get('doc_send_status') or 200
        self.max_row_limit = config_['send-setting'].get('max_row_limit')
        self.check_duplicated = config_['send-setting'].get('check_duplicated')
        self.process_lagtime = config_['send-setting'].get('process_lagtime') or 0
        self.machine_id = config_['send-setting'].get('machine_id')
        self.service_id = config_['send-setting'].get('service_id')
        self.service_name = config_['send-setting'].get('service_name')
        self.receiver_cc = config_['send-setting'].get('receiver_cc')
        self.sender_name = config_['send-setting'].get('sender_name')
        self.webapp_attachment_path = config_['import-setting'].get('webapp_attachment_path')
        self.max_mail_size = config_['send-setting'].get('max_mail_size')
        self.tmp_path = config_['send-setting'].get('tmp_path') or BASE_PATH / '.tmp'
        if not self.tmp_path.exists():
            self.tmp_path.mkdir(parents=True, exist_ok=True)
        self.xml_attachment_filename = config_['send-setting'].get('xml_attachment_filename')

    def update_km_recvsendinfo(self, sendflag, objid):
        send_time = self.date_now.strftime('%Y-%m-%d %H:%M:%S')
        sql_update = ''
        if db1_type == 1 or db1_type == 4 or db1_type == 5:
            sql_update = f"update {table_km_recvsendinfo} SET sendflag={sendflag},sendtime=TO_DATE('{send_time}','yyyy-mm-dd HH24:MI:SS') where objid={objid}"
        elif db1_type == 2:
            sql_update = f"update {table_km_recvsendinfo} SET sendflag={sendflag},sendtime='{send_time}' where objid={objid}"
        elif db1_type == 3:
            sql_update = f"update {table_km_recvsendinfo} SET sendflag={sendflag},sendtime=TO_TIMESTAMP('{send_time}','yyyy-mm-dd HH24:MI:SS') where objid={objid}"
        SEND_DB.update(sql_update)
        return

    def get_attachment_path(self, attach, attach_path, objid):
        store_path = attach.get('STOREPATH')
        if Path(store_path).exists():
            attach['ContentFilename'] = store_path
            logging.info("使用attachment表的附件存储路径")
            return
        logging.info("attachment表的附件存储路径不存在。检查其他路径。")
        if not attach_path:
            logging.info("配置项webappattachmentpath为空")
            return
        if not Path(attach_path).exists():
            logging.info(f"配置项webappattachmentpath：{attach_path}. 路径不存在")
            return
        p = Path(attach_path) / (str(attach.get('CREATETIMESTAMP'))[:10].replace("-", '') + "/")
        if not p.exists():
            logging.info(f"路径{p}不存在")
            self.update_km_recvsendinfo(-100, objid)
            return
        f = str(attach.get('ID'))
        try_file_list = []
        if attach.get('NAME') and attach.get('NAME')[-1] != ".":
            f += '.' + attach.get('NAME').split(".")[-1]
            try_file_list.append(p.joinpath(f))
        else:
            extensions = ['pdf', 'dat']
            for ext in extensions:
                try_file_list.append(p.joinpath(f'{f}.{ext}'))
        logging.info("Try to use:" + f)
        found = False
        for i in try_file_list:
            if i and Path(i).exists():
                found = True
                attach['ContentFilename'] = f
                break
        if not found:
            logging.info(f"路径{attach_path}不存在")
            self.update_km_recvsendinfo(-100, objid)
        return

    def add_attach(self, message, attach, attach_path, objid, _totalAttachmentSize):
        if Path(attach_path).exists():
            _fileSize = Path(attach_path).stat().st_size
            _totalAttachmentSize += _fileSize
            logging.info("附件大小:" + str(_fileSize) + "\t附件总大小:" + str(_totalAttachmentSize))
            message['attach'].append([attach['NAME'], attach_path, 'application/pdf'])
            return True
        else:
            logging.info("未找到附件, send cancel and continue next! File Not Found:" + (
                    attach.get('ContentFilename') or ''))
            self.update_km_recvsendinfo(-100, objid)
            return False

    @staticmethod
    def load_customer_info(customer_id):
        sql = f"select * from {table_km_customerinfo} where objid = '{customer_id}'"
        return deal_date(SEND_DB.search(sql, cols_info=True))[0]

    @staticmethod
    def load_docrecievelog_info(uid):
        sql = ''
        if db1_type == 1 or db1_type == 4:
            sql = f'''select * from {table_km_docrecievelog} where "UID" = '{uid}' '''
        elif db1_type == 2 or db1_type == 3 or db1_type == 5:
            sql = f"select * from {table_km_docrecievelog} where UID = '{uid}' "
        return deal_date(SEND_DB.search(sql, cols_info=True))[0]

    @staticmethod
    def load_attachment_info(doc_id):
        sql = f"select * from {table_attachment} where docid = {doc_id}"
        return deal_date(SEND_DB.search(sql, cols_info=True))

    def get_mails_to_be_send(self):
        final_sql = ""
        sql = 'SELECT '
        if SEND_CONF['send-setting'].get('optimizer'):
            sql += SEND_CONF['send-setting'].get('optimizer')
        sql += (
                    "a.*,d.recieve_version,d.SMTPSERVER,d.SMTPPORT FROM " + table_km_recvsendinfo + " a INNER JOIN " + table_km_customerinfo + " d ON a.CUSTOMERID=d.Objid "                                                                                                                                                                            "and d.Removetag='0' and d.EMAIL is not null "
                                                                                                                                               "AND a.SENDFLAG=0 AND a.REMOVETAG='0'")
        if self.delay_minutes:
            logging.info(f"配置了延迟时间（delayminutes）：{self.delay_minutes}")
            now = self.date_now - datetime.timedelta(minutes=int(self.delay_minutes))
            if db1_type == 1 or db1_type == 4 or db1_type == 5:
                sql += " AND a.RECVTIME<TO_DATE('" + now.strftime('%Y-%m-%d %H:%M') + "', 'YYYY-MM-DD HH24:MI')"
            elif db1_type == 2:
                sql += " AND a.RECVTIME<'" + now.strftime('%Y-%m-%d %H:%M') + "' "
            elif db1_type == 3:
                sql += " AND a.RECVTIME<TO_TIMESTAMP('" + now.strftime('%Y-%m-%d %H:%M') + "', 'YYYY-MM-DD HH24:MI')"
        if self.include_thread_id:
            logging.info(f"配置了threadid限制：{self.include_thread_id}")
            sql += f" AND d.THREADID IN ({self.include_thread_id})"
        if self.include_customer_limit:
            logging.info(f"配置了发送的customerid：{self.include_customer_limit}")
            sql += f" AND a.CUSTOMERID IN ({self.include_customer_limit})"
        if self.exclude_customer_limit:
            logging.info(f"配置了不发送的customerid：{self.exclude_customer_limit}")
            sql += f" AND NOT a.CUSTOMERID IN ({self.exclude_customer_limit})"
        if self.exclude_broker_list:
            logging.info(f"配置了不发送的brokerid：{self.exclude_broker_list}")
            sql += f" INNER JOIN {table_docreport} e ON a.DOCID=e.OBJID AND NOT e.BROKERID "
            if self.exclude_broker_list.split(",")[-1]:
                sql += f"IN ({self.exclude_broker_list})"
            else:
                sql += f"={self.exclude_broker_list}"
        if self.offset_days:
            logging.info(f"配置了延迟天数（offsetdays）：{self.offset_days}")
            if db1_type == 1 or db1_type == 4 or db1_type == 5:
                sql += " AND a.RECVTIME>TO_DATE('" + (
                        datetime.datetime.now() - datetime.timedelta(days=int(self.offset_days))).strftime(
                    '%Y-%m-%d') + "', 'YYYY-MM-DD')"
            elif db1_type == 2:
                sql += " AND a.RECVTIME>'" + (
                        datetime.datetime.now() - datetime.timedelta(days=int(self.offset_days))).strftime(
                    '%Y-%m-%d') + "' "
            elif db1_type == 3:
                sql += " AND a.RECVTIME>TO_TIMESTAMP('" + (
                        datetime.datetime.now() - datetime.timedelta(days=int(self.offset_days))).strftime(
                    '%Y-%m-%d') + "', 'YYYY-MM-DD')"
        sql += f" AND ( NOT EXISTS  ( SELECT OBJID FROM {table_docgeneral} b where b.objid=a.docid ) OR EXISTS ( SELECT OBJID FROM {table_docgeneral} b where b.objid=a.docid AND (b.REMOVETAG='1' OR "
        if self.doc_send_status_list:
            logging.info(f"配置了发送的doc status状态：{self.doc_send_status_list}")
            sql += f"b.STATUS in ({self.doc_send_status_list})"
        else:
            sql += f"b.STATUS={self.doc_send_status}"
        sql += "))) ORDER BY a.RECVTIME DESC"
        if db1_type == 1 or db1_type == 4 or db1_type == 5:
            final_sql = f"SELECT * FROM ({sql}) WHERE ROWNUM<{self.max_row_limit}"
        elif db1_type == 3:
            final_sql = f"SELECT * FROM ({sql}) as foo limit {self.max_row_limit}"
        elif db1_type == 2:
            final_sql = f"SELECT * FROM (({sql}) as a) limit {self.max_row_limit}"
        dt_l = SEND_DB.search(final_sql, cols_info=True)
        dt = deal_date(dt_l)

        return dt


def single_send_process(index, row, send_dao):
    try:
        obj_id = int(row['OBJID'])
        doc_id = int(row['DOCID'])
        customer_id = int(row['CUSTOMERID'])
        smtp_server_direct = row.get('SMTPSERVER') or ''
        smtp_port_direct = row.get('SMTPPORT') or '0'
        cusEntityObj = send_dao.load_customer_info(customer_id)
        customer_email = cusEntityObj['EMAIL']
        receiever_email = cusEntityObj['USERNAME'] + '@' + cusEntityObj.get('SERVER')[5:]
        customer_email_array = re.split(r'[;；]', customer_email)
        if receiever_email in customer_email_array:
            logging.warning(f"待发送邮箱（收件人）存在接收邮箱{receiever_email}，跳过发送")
            return 'continue'
        logging.info(
            f"Index:{index}\tObjid:{obj_id}\tDoc:{doc_id}\tCustomer:{customer_id}\tSendTo:"
            f"{customer_email}\tSmtpDirect:{smtp_server_direct}\tPort:{smtp_port_direct}")
        if not customer_email:
            logging.warning(
                f"Doc[{doc_id}]\tKM_RECVSENDINFO[{obj_id}]\tkm_customerinfo表EMAIL字段（发送地址）为空! 取消发送. Continue... ")
            return 'continue'
        recv_version = row.get("RECIEVE_VERSION") or row.get('recieve_version') or 0
        uid = str(row["UID"]).strip() or ''
        logging.info("UID=" + uid)
        if 'SYSTEMVERSION' in cusEntityObj.keys():
            system_version = cusEntityObj.get('SYSTEMVERSION', 0)
            logging.info(f"System Version:{system_version}")
        else:  # ？
            logging.warning("km_customerinfo表无字段SYSTEMVERSION! 请检查。")
            return 'continue'
        # industryVersion = cusEntityObj.get('INDUSTRYVERSION', 0)  # 表km_customerinfo无IndustryVersion字段
        logging.info(f"Receive Version:{recv_version:.0f}")
        secret_level = cusEntityObj.get('SECRETLEVEL', -1)

        # 邮件查重
        if send_dao.check_duplicated:
            logging.info("配置项checkduplicated为True，开始检查重复报告！")
            sqla = f"select * from {table_km_recvsendinfo} where customerid={customer_id} and docid={doc_id} and sendflag=1 order by objid desc"
            r = deal_date(SEND_DB.search(sqla, cols_info=True))
            if r:
                logging.info(f"报告已发送（重复）! 不再重复发送. Doc[{doc_id}] KM_RECVSENDINFO[{obj_id}]"
                             f" CUSTOMERID[{customer_id}] Old RowId:{r['OBJID']} Old Send Date:{r['SENDTIME']}")
                return 'continue'

        try:
            general_sql = f"select * from {table_docgeneral} where objid={doc_id}"
            report_sql = f"select * from {table_docreport} where objid={doc_id}"
            entity_sql = f"select * from {table_entity} where entity_id = (select clsid from {table_km_customerinfo} where objid = {customer_id})"
            doc_general = deal_date(SEND_DB.search(general_sql, cols_info=True))[0]
            doc_report = deal_date(SEND_DB.search(report_sql, cols_info=True, get_summary=True))[0]
            entity = deal_date(SEND_DB.search(entity_sql, cols_info=True))[0]
            # doc_general['docreport'] = doc_report
            doc_general['entity'] = entity
        except Exception as err:
            logging.info(f"Doc Invalid (deleted): Doc[{doc_id}]\tError:{err}")
            logging.info("set status to invalid -100")
            return 'continue'
        logging.info(
            f"====doc[{doc_id}] load ok! Ready send to:{customer_email}\tCustomer ID: {customer_id}")
        doc_status = doc_general.get('STATUS')
        if send_dao.doc_send_status_list:
            send_status_list = send_dao.doc_send_status_list.split(',')
            if not doc_general.get('STATUS') in send_status_list:
                logging.info(
                    f"Doc[{doc_id}]\tStatus（状态码）:{doc_status} Not Ready Multiple! Status list:{send_status_list}\tContinue...")
                return 'continue'
        else:
            if int(doc_status) != int(send_dao.doc_send_status):
                logging.info(
                    f"Doc[{doc_id}]\tStatus（状态码）:{doc_status} Not Ready Single! Send status:{send_dao.doc_send_status}\tContinue...")
                return 'continue'
        if int(secret_level) != -1 and int(doc_general.get("SECERT")) > int(secret_level):
            logging.info(
                f"Doc[{doc_id}]\tSecret(文档密级):{doc_general.get('SECERT')} higher than broker secretlevel:{secret_level}\tContinue...")
            return 'continue'
        _targetTime = datetime.datetime.now() - datetime.timedelta(minutes=int(send_dao.process_lagtime))
        if isinstance(doc_report["ARCHIVETIME"], datetime.datetime):
            doc_report["ARCHIVETIME"] = doc_report["ARCHIVETIME"].strftime("%Y-%m-%d %H:%M:%S")
        if doc_report["ARCHIVETIME"] > _targetTime.strftime(
                "%Y-%m-%d %H:%M:%S"):
            logging.info(f"Doc[{doc_id}] ProcessTime[Archive:" + doc_report["ARCHIVETIME"] + "\tTime Limit:" + _targetTime.strftime(
                "%Y-%m-%d %H:%M:%S") + "] Not Reached!")
            return 'continue'

        sql_doctype = f"select * from {table_doctype} where objid = {doc_general.get('DOCTYPEID')}"
        doc_type = deal_date(SEND_DB.search(sql_doctype, cols_info=True))
        if not doc_type:
            logging.warning("DOCTYPEID未查到。Continue...")
            return 'continue'
        # doc_general['docType'] = doc_type
        # 待发送message结构
        message = {
            # 'title':doc_general.get('TITLE'),
            'sender': str(send_dao.sender_name),
            'receiver': ','.join(customer_email_array),
            'attach': [],
            'date': doc_report.get('WRITETIME')
        }
        if send_dao.machine_id:
            logging.info(f"配置了邮箱标识（machineid）：{send_dao.machine_id}")
            qidheader = str(send_dao.machine_id) + "_"
            if send_dao.service_id:
                qidheader += send_dao.service_id
            else:
                qidheader += send_dao.service_name
                qidheader += "_" + str(obj_id)
                logging.info(f"add qid: {qidheader}")
            message['sender'] = f"message['sender']({qidheader})"

        if send_dao.receiver_cc:
            message['receiver'] = message['receiver'] + ',' + str(send_dao.receiver_cc)
            logging.info(f"message added cc:{send_dao.receiver_cc}")

        message['title'] = doc_report.get('ORIGINALTITLE').replace("??", "").replace("\\",
                                                                                           "").replace(
            "\r\n", "").replace("\n", "").strip() or doc_general.get('TITLE')

        if not message['title']:
            message['title'] = "Untitled"
        message['body'] = doc_report.get('SUMMARY')
        attachments_info = send_dao.load_attachment_info(doc_id)
        # doc_general['ATTACHMENTS'] = attachments_info

        att_count = len(attachments_info)
        _totalAttachmentSize = 0
        logging.info(f"附件数:{att_count}")
        for attach in attachments_info:
            # logging.info(f"attach==>{attach}")
            store_path = attach.get('STOREPATH')
            logging.info(f"Attachment[{attach.get('OBJID')}]\t{attach.get(
                'NAME')}\tStorePath:{store_path}")  # 不同storepath打印不同的storepath
            if int(attach.get('STORETYPE')) == 0:
                content = attach.get('CONTENT')
                if content:
                    attachfile_name = f"{attach.get('OBJID')}.{attach.get('NAME').split('.')[-1]}"
                    file_path = send_dao.tmp_path.joinpath(attachfile_name)
                    if isinstance(content, bytes):
                        logging.info("content 字段类型为 bytes")
                        with open(file_path, "wb") as f:
                            f.write(bytes(content))
                    elif isinstance(content, memoryview):
                        logging.info("content 字段类型为 memoryview")
                        with open(file_path, 'wb') as f:
                            f.write(bytes(content))
                    else:
                        logging.info("content 字段类型为 string")
                        with open(file_path, 'w') as f:
                            f.write(content)
                    attach['ContentFilename'] = file_path
                    res = send_dao.add_attach(message, attach, file_path, obj_id, _totalAttachmentSize)
                    if not res:
                        continue
                else:
                    logging.warning(f"附件内容为空，Content：{content}. Continue ...")
                    continue
            elif int(attach.get('STORETYPE')) == 5:
                from docwork.utils.oss import S3Client
                s3 = S3Client(SEND_CONF['import-setting'].get('use-oss'))
                keyname = attach.get('STOREPATH').split('|')[2]
                content = s3.get_content(RUN_CONF.oss_config.bucket_name, key_name=keyname)
                attachfile_name = f"{attach.get('OBJID')}.{attach.get('NAME').split('.')[-1]}"
                # 若没有配TmpPath, 默认下载到当前路径下的.tmp/，后续删除对应的下载文件
                file_path = send_dao.tmp_path.joinpath(attachfile_name)
                with open(file_path, "wb") as f:
                    f.write(content)
                attach['ContentFilename'] = file_path
                res = send_dao.add_attach(message, attach, file_path, obj_id, _totalAttachmentSize)
                if not res:
                    continue
            else:
                if not store_path:
                    logging.info("附件内容没存储在数据库中!")
                else:
                    send_dao.get_attachment_path(attach, send_dao.webapp_attachment_path, obj_id)
                    if attach.get('ContentFilename'):
                        res = send_dao.add_attach(message, attach, attach.get('ContentFilename'), obj_id,
                                                  _totalAttachmentSize)
                        if not res:
                            continue
        if send_dao.max_mail_size and (_totalAttachmentSize > int(send_dao.max_mail_size)):
            send_dao.update_km_recvsendinfo(-100, obj_id)
            return 'continue'
        recievelog_infos = send_dao.load_docrecievelog_info(uid)
        xml_attach = send_dao.tmp_path / send_dao.xml_attachment_filename
        get_xml(xml_attach, recievelog_infos, send_dao.date_now)
        message['attach'].append([send_dao.xml_attachment_filename, xml_attach, 'application/pdf'])
        logging.info(f"guid xml:{xml_attach}")

        # 邮件发送
        send_ok = sendmail(message, SEND_CONF)
        if send_ok:
            send_dao.update_km_recvsendinfo(1, obj_id)
            logging.info(f"RecvSendInfo[{row['OBJID']}]\t邮件发送成功！")
        else:
            logging.info("邮件发送失败")
            send_dao.update_km_recvsendinfo(-10, obj_id)

        for filename in os.listdir(send_dao.tmp_path):
            if os.path.isfile(send_dao.tmp_path.joinpath(filename)):
                os.remove(send_dao.tmp_path.joinpath(filename))
                logging.info(f"删除文件:{filename}")
    except Exception as err:
        logging.info(traceback.format_exc())
        logging.error(f"当前待发送的邮件（RecvSendInfo[{row['OBJID']}]）出现异常，Error:{err}，Continue...")
        return 'continue'


def sent():
    try:
        send_dao = SendDao(config_=SEND_CONF)
        dt = send_dao.get_mails_to_be_send()
        _total = len(dt)
        _i = 0
        logging.info(f"一共{_total}篇报告")
        
        # 获取发送间隔配置
        delay_seconds = SEND_CONF['send-setting'].get('delay_seconds', 0)
        # 判断邮件发送间隔时间*max_row_limit是否大于发送服务的间隔时间
        max_row_limit = SEND_CONF['send-setting'].get('max_row_limit', 0)
        send_sleep_time = RUN_CONF['run-setting'].get('send_sleep_time', 0)
        if delay_seconds * max_row_limit > send_sleep_time:
            logging.error(f"邮件发送间隔时间*{max_row_limit}大于发送服务的间隔时间{send_sleep_time}，可能会导致邮件发送失败，请重新设置")
            raise ValueError(f"邮件发送间隔时间*{max_row_limit}大于发送服务的间隔时间{send_sleep_time}，可能会导致邮件发送失败，请重新设置")
        
        for row in dt:
            _i += 1
            res = single_send_process(_i, row, send_dao)
            if res == 'continue':
                continue
            
            # 在邮件发送之间添加延迟，除了最后一封邮件
            if _i < _total and delay_seconds > 0:
                logging.info(f"等待 {delay_seconds} 秒后继续发送下一封邮件...")
                import time
                time.sleep(delay_seconds)
                
    except Exception as err:
        logging.error(f"报告发送出现异常(func:sent),Error:{err}")

